﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

namespace StartStop.Helpers
{

    public class Sql
    {

        public const string LiveConnectionString = "data source=v2.startstop.me;initial catalog=startstop;persist security info=True;user id=sa;password=PASSWORD;";

        public static string ExecuteScalar(string ExecutableSQL)
        {
            string _return = "";
            SqlConnection thisConnection = new SqlConnection(LiveConnectionString);
            thisConnection.Open();


            SqlCommand executeScaler = new SqlCommand(ExecutableSQL, thisConnection);

            if (executeScaler.ExecuteScalar() != null)
            {
                _return = executeScaler.ExecuteScalar().ToString();
            }

            thisConnection.Close();

            return _return;
        }

        /// <summary>
        /// This is the same, but uses a paramter.
        /// </summary>
        /// <param name="ExecutableSQL"></param>
        /// <param name="PassedValue"></param>
        /// <returns></returns>
        public static string ExecuteScalar(string ExecutableSQL, string PassedValue)
        {
            string _return = "";
            SqlConnection thisConnection = new SqlConnection(LiveConnectionString);
            thisConnection.Open();


            SqlCommand executeScaler = new SqlCommand(ExecutableSQL, thisConnection);
            executeScaler.Parameters.AddWithValue("@passedvalue", PassedValue); 

            if (executeScaler.ExecuteScalar() != null)
            {
                _return = executeScaler.ExecuteScalar().ToString();
            }

            thisConnection.Close();

            return _return;
        }


        public static DataTable ReturnDataTable(string ExecutableSQL)
        {
            string _SQL = ExecutableSQL;
            SqlDataAdapter da = new SqlDataAdapter();

            SqlConnection thisConnection = new SqlConnection(LiveConnectionString);
            thisConnection.Open();
            SqlCommand loadData = new SqlCommand(_SQL, thisConnection);
            DataTable _returnDataTable = new DataTable();
            da.SelectCommand = loadData;
            da.Fill(_returnDataTable);
            // always close the connection. :) 
            thisConnection.Close();

            return _returnDataTable;
        }



    }

}
